package com.optimus.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcDaoSupport;

import com.optimus.model.mapped.ProductBean;

/**
 * @author Optimus
 * 
 */
public class ProductDAOImpl extends SimpleJdbcDaoSupport implements ProductDAO
{
	
	@Override
	public void insertIntoDB(ProductBean entity)
	{
		String sqlCommand = "insert into product (product_id, product_name, price, picture_location, category_id, description) values (?, ?, ?, ?, ?, ?)";
		getJdbcTemplate().update(
				sqlCommand,
				new Object[] { null, entity.getName(), Double.valueOf(entity.getPrice()),
						entity.getPicturePath(), Integer.valueOf(entity.getCategoryId()),
						entity.getDescription() });

	}

	@Override
	public ProductBean selectEntityFromDbById(int id)
	{
		String query = "SELECT * FROM product WHERE product_id = ?";

		return (ProductBean) getJdbcTemplate().queryForObject(query,
				new Object[] { Integer.valueOf(id) }, new RowMapper<ProductBean>()
				{
					public ProductBean mapRow(ResultSet resultSet, int rowNum) throws SQLException
					{
						return new ProductBean(resultSet.getInt("product_id"), resultSet
								.getString("product_name"), resultSet.getDouble("price"), resultSet
								.getString("picture_location"), resultSet.getInt("category_id"),
								resultSet.getString("description"));
					}
				});
	}

	@Override
	public List<ProductBean> selectAllProductsByCategory(int categoryId, String orderBy)
	{
		String sql;
		Object[] params = null;
		orderBy = orderBy == null ? "product_id" : orderBy;
		if (categoryId == 0)
		{
			sql = "select * from product order by " + orderBy + " asc";
		}
		else
		{
			sql = "select * from product where category_id=? order by " + orderBy + " asc";
			params = new Object[] { categoryId };
		}

		return getJdbcTemplate().query(sql, params, new RowMapper<ProductBean>()
		{
			public ProductBean mapRow(ResultSet rs, int rowNum) throws SQLException
			{
				ProductBean product = new ProductBean();
				product.setId(Integer.valueOf(rs.getString("product_id")));
				product.setName(rs.getString("product_name"));
				product.setPrice(Double.valueOf(rs.getString("price")));
				product.setPicturePath(rs.getString("picture_location"));
				product.setCategoryId(Integer.valueOf(rs.getInt("category_id")));
				product.setDescription(rs.getString("description"));
				return product;
			}
		});
	}

	@Override
	public void updateIntoDB(ProductBean entity)
	{
		String sqlCommand = "UPDATE product SET product_name = ?, price = ?, picture_location = ?, category_id = ?, description = ? WHERE product_id = ?";
		getJdbcTemplate().update(
				sqlCommand,
				new Object[] { entity.getName(), Double.valueOf(entity.getPrice()),
						entity.getPicturePath(), Integer.valueOf(entity.getCategoryId()),
						entity.getDescription(), Integer.valueOf(entity.getId()) });

	}

	@Override
	public List<ProductBean> selectAllProductsBySearch(int categoryId, String searchName)
	{
		String sql;
		if (categoryId == 00)
		{
			sql = "SELECT * FROM product  WHERE product_name REGEXP '(" + searchName + ")'";
		}
		else
		{
			sql = "SELECT * FROM product  WHERE product_name REGEXP '(" + searchName
					+ ")'and category_id=" + categoryId + " ";
		}

		return getJdbcTemplate().query(sql, new RowMapper<ProductBean>()
		{
			public ProductBean mapRow(ResultSet rs, int rowNum) throws SQLException
			{
				ProductBean product = new ProductBean();
				product.setId(Integer.valueOf(rs.getString("product_id")));
				product.setName(rs.getString("product_name"));
				product.setPrice(Double.valueOf(rs.getString("price")));
				product.setPicturePath(rs.getString("picture_location"));
				product.setCategoryId(Integer.valueOf(rs.getInt("category_id")));
				product.setDescription(rs.getString("description"));
				return product;
			}
		});
	}

	@Override
	public void updateNewPictureIntoDB(int id, String picturePath)
	{
		String sqlCommand = "UPDATE product SET picture_location = ? WHERE product_id = ?";
		getJdbcTemplate().update(sqlCommand, new Object[] { picturePath, id });
	}

	@Override
	public void updateProduct(int productId, String productName, Double productPrice,
			String pictureLocation, int catalogId)
	{
		String sqlCommand = "UPDATE product SET product_name = ?, price = ?, picture_location = ? WHERE product_id = ?";
		getJdbcTemplate().update(sqlCommand,
				new Object[] { productName, productPrice, pictureLocation, productId });
	}

	@Override
	public void deleteProduct(int productId)
	{
		String sqlCommand = "DELETE FROM product WHERE product_id = ?";
		getJdbcTemplate().update(sqlCommand, new Object[] { productId });
	}
}
